package com.donnie.spinner;

import com.donnie.spinner.util.MySQLiteOpenHelper;

import android.app.Activity;
import android.content.ContentValues;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.os.Bundle;
import android.view.Menu;
import android.view.MenuItem;
import android.view.View;
import android.view.View.OnClickListener;
import android.widget.Button;
import android.widget.Toast;

public class DB_MainActivity extends Activity {
	
	final String MYTAB = "t_score";
	final String MYNAME = "name";
	final String MYSCORE = "score";
	
	MySQLiteOpenHelper helper;
	private Button selectData,createDatabase,createTable,insertData,updateData,deleteData;
	
	public void init(){
		selectData = (Button) findViewById(R.id.selectData);
		createDatabase = (Button) findViewById(R.id.createDatabase);
		createTable = (Button) findViewById(R.id.createTable);
		insertData = (Button) findViewById(R.id.insertData);
		updateData = (Button) findViewById(R.id.updateData);
		deleteData = (Button) findViewById(R.id.deleteData);
	}

	@Override
	protected void onCreate(Bundle savedInstanceState) {
		super.onCreate(savedInstanceState);
		setContentView(R.layout.activity_db__main);
		
		init();
		
		createDatabase.setOnClickListener(new OnClickListener() {
			
			@Override
			public void onClick(View v) {
				// TODO Auto-generated method stub
				helper = new MySQLiteOpenHelper(DB_MainActivity.this, "mydb.db", null, 1);
				
				SQLiteDatabase database = helper.getWritableDatabase();
				
				Toast.makeText(DB_MainActivity.this, "数据库创建成功", 1000).show();
			}
		});
		
		createTable.setOnClickListener(new OnClickListener() {
			
			@Override
			public void onClick(View v) {
				// TODO Auto-generated method stub
				helper = new MySQLiteOpenHelper(DB_MainActivity.this, "mydb.db", null, 1);
				
				SQLiteDatabase database = helper.getWritableDatabase();
				
				database.execSQL("create table student(id INTEGER PRIMARY KEY autoincrement,name text);");
				database.execSQL("create table "+MYTAB+"("+MYNAME+" text,"+MYSCORE+" integer);");
				
				Toast.makeText(DB_MainActivity.this, "数据表创建成功", 1000).show();
			}
		});
		
		insertData.setOnClickListener(new OnClickListener() {
			
			@Override
			public void onClick(View v) {
				// TODO Auto-generated method stub
				SQLiteDatabase database = helper.getWritableDatabase();
				
				database.execSQL("insert into student(name) values('mike')");
				
				ContentValues cv = new ContentValues();
				cv.put("name", "mary");
				database.insert("student", null, cv);
				cv.clear();
				
				database.execSQL("insert into "+MYTAB+" values('ray',95)");  
				database.execSQL("insert into "+MYTAB+" values('tom',85)");  
				database.execSQL("insert into "+MYTAB+" values('jone',90)");
                cv.put(MYNAME, "jack");  
                cv.put(MYSCORE, 78);  
                database.insert(MYTAB, null, cv);  
                cv.clear();
                
                Toast.makeText(DB_MainActivity.this, "数据插入成功", 1000).show();
			}
		});
		
		updateData.setOnClickListener(new OnClickListener() {
			
			@Override
			public void onClick(View v) {
				// TODO Auto-generated method stub
				SQLiteDatabase database = helper.getWritableDatabase();
				
				ContentValues cv = new ContentValues();  
                cv.put("name", "mary key");
                
                database.update("student", cv, "id=?", new String[]{"1"});
                
                Toast.makeText(DB_MainActivity.this, "数据修改成功", 1000).show();
			}
		});
		
		deleteData.setOnClickListener(new OnClickListener() {
			
			@Override
			public void onClick(View v) {
				// TODO Auto-generated method stub
				SQLiteDatabase database = helper.getWritableDatabase();
				
				database.delete("student", "id=?", new String[]{"1"});
				
				Toast.makeText(DB_MainActivity.this, "数据删除成功", 1000).show();
			}
		});
		
		selectData.setOnClickListener(new OnClickListener() {
			
			@Override
			public void onClick(View v) {
				// TODO Auto-generated method stub
				SQLiteDatabase database = helper.getReadableDatabase();
				
				Cursor cursor = database.query("student", new String[]{"id","name"}, "id=?", new String[]{"1"}, null, null, null);
				
				String id = null;
				String name = null;
				
				while(cursor.moveToNext()){
					id = cursor.getString(cursor.getColumnIndex("id"));
					name = cursor.getString(cursor.getColumnIndex("name"));
				}
				
				Toast.makeText(DB_MainActivity.this, "查询数据为：id="+id+"\n name="+name, 1000).show();
			}
		});
	}

	@Override
	public boolean onCreateOptionsMenu(Menu menu) {
		// Inflate the menu; this adds items to the action bar if it is present.
		getMenuInflater().inflate(R.menu.db__main, menu);
		return true;
	}

	@Override
	public boolean onOptionsItemSelected(MenuItem item) {
		// Handle action bar item clicks here. The action bar will
		// automatically handle clicks on the Home/Up button, so long
		// as you specify a parent activity in AndroidManifest.xml.
		int id = item.getItemId();
		if (id == R.id.action_settings) {
			return true;
		}
		return super.onOptionsItemSelected(item);
	}
}
